﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace ShenLan.Common
{
    public class DBAccessor
    {
        public class Configuration
        {
            //构造函数一般是public的,进行实例化
            private Configuration() { }//private不希望对Configuration进行实例化,因为它的属性是静态[static]的
            private static string connectionString = null;
            public static string ConnectionString
            {
                get
                {
                    if (connectionString == null)
                    {
                        //connectionString = ConfigurationManager.AppSettings["DBconnectionString"];
                        connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["TN_DbEntity"].ConnectionString;
                    }
                    return connectionString;
                }
            }
        }

        private static SqlConnection conn = new SqlConnection(Configuration.ConnectionString);//静态方法 static 与下面的一样

        /// <summary>
        /// 执行命令,不返回数据信息,只返回受影响的行数
        /// </summary>
        /// <param name="cmd">执行命令,insert,delete,update命令</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteSQL(string sql) //与上面的一样
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            if (cmd == null)
            {
                throw new ArgumentNullException("cmd");
            }
            int n = 0;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                n = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new DBException(ex); //抛出经过处理的错误信息,见类\DataAccess\DBException.cs
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return n;
        }


        /// <summary>
        /// 执行命令,返回一项数据
        /// </summary>
        /// <param name="cmd">执行命令,select 命令</param>
        /// <returns>返回一项数据</returns>
        public static object GetScalar(string sql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            if (cmd == null)
            {
                throw new ArgumentNullException("cmd");
            }
            object o = 0;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                o = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new DBException(ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return o;
        }


        /// <summary>
        /// 执行命令,返回数据集
        /// </summary>
        /// <param name="cmd">执行命令,select 命令</param>
        /// <returns>返回数据集</returns>
        public static DataSet QueryData(string sql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            if (cmd == null)
            {
                throw new ArgumentNullException("cmd");
            }
            cmd.Connection = conn;
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                ada.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new DBException(ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return ds;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            if (cmd == null)
            {
                throw new ArgumentNullException("cmd");
            }
            cmd.Connection = conn;
            SqlDataReader rdr;
            try
            {
                conn.Open();

                //关闭DataReader时候关闭连接
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw new DBException(ex);
            }
            return rdr;
        }

    }

    //抛出异常类
    public class DBException : Exception
    {
        public DBException(Exception innerException)
            : base("不能访问数据库", innerException)
        { }
    }
}